﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace JamesThew.Dashboard
{
    public class PaymentDB
    {

        public List<String[]> Get(int Year, int MonthMin, int MonthMax, int DateMin, int DateMax, int MonthExpiredMin, int MonthExpiredMax,
            int DateExpiredMin, int DateExpiredMax, String PaymentMethod, String PaymentPackage, String PaymentStatus)
        {
            List<String[]> lp = new List<String[]>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("AdvancedGetPayment", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@Year", Year));
            cmd.Parameters.Add(new SqlParameter("@MonthMin", MonthMin));
            cmd.Parameters.Add(new SqlParameter("@MonthMax", MonthMax));
            cmd.Parameters.Add(new SqlParameter("@DateMin", DateMin));
            cmd.Parameters.Add(new SqlParameter("@DateMax", DateMax));
            cmd.Parameters.Add(new SqlParameter("@MonthExpiredMin", MonthExpiredMin));
            cmd.Parameters.Add(new SqlParameter("@MonthExpiredMax", MonthExpiredMax));
            cmd.Parameters.Add(new SqlParameter("@DateExpiredMin", DateExpiredMin));
            cmd.Parameters.Add(new SqlParameter("@DateExpiredMax", DateExpiredMax));
            cmd.Parameters.Add(new SqlParameter("@PaymentMethod", PaymentMethod));
            cmd.Parameters.Add(new SqlParameter("@PaymentPackage", PaymentPackage));
            cmd.Parameters.Add(new SqlParameter("@PaymentStatus", PaymentStatus));

            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                String YearCreated = dt.Rows[i][0].ToString();
                String MonthCreated = dt.Rows[i][1].ToString();
                String DateCreated = dt.Rows[i][2].ToString();
                String PaymentID = dt.Rows[i][3].ToString();
                String UserID = dt.Rows[i][4].ToString();
                String UserLogin = dt.Rows[i][5].ToString();
                String UserPaymentMethod = dt.Rows[i][6].ToString();
                String UserPaymentPackage = dt.Rows[i][7].ToString();
                String DateExpired = dt.Rows[i][8].ToString();
                String UserPaymentStatus = dt.Rows[i][9].ToString();

                String[] S = new String[] { YearCreated, MonthCreated, DateCreated, PaymentID, UserID,
                    UserLogin, UserPaymentMethod, UserPaymentPackage, DateExpired, UserPaymentStatus
                };
                lp.Add(S);
            }

            con.Close();

            return lp;
        }

        public List<String[]> GetUserNotifications(int UserID)
        {
            List<String[]> lp = new List<String[]>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("GetPaymentNotifications", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@UserID", UserID));

            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                String sUserID = UserID.ToString();
                String UserLogin = dt.Rows[i][1].ToString();
                String PaymentID = dt.Rows[i][12].ToString();
                String UserEmail = dt.Rows[i][5].ToString();
                String UserPaymentMethod = dt.Rows[i][14].ToString();
                String UserPaymentPackage = dt.Rows[i][15].ToString();
                String DateCreated = dt.Rows[i][16].ToString();
                String DateExpired = dt.Rows[i][17].ToString();
                String TimeLeft = dt.Rows[i][20].ToString();

                String[] S = new String[] { sUserID, UserLogin, PaymentID, UserEmail, UserPaymentMethod, UserPaymentPackage, DateCreated, DateExpired, TimeLeft };
                
                lp.Add(S);
            }

            con.Close();

            return lp;
        }



        public Boolean Delete(int CategoryID)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("DeleteCategory", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@CategoryID", CategoryID));

            Int32 check = cmd.ExecuteNonQuery();
            con.Close();
            if (check > 0) return true;
            return false;
        }

        public Boolean Add(Category cat)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("InsertCategory", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@CategoryName", cat.CategoryName));
            cmd.Parameters.Add(new SqlParameter("@CategoryDescription", cat.CategoryDescription));
            cmd.Parameters.Add(new SqlParameter("@CategoryParent", cat.CategoryParent));


            cmd.Parameters.Add(new SqlParameter("@CategoryID", SqlDbType.Int, 6));
            cmd.Parameters["@CategoryID"].Direction = ParameterDirection.Output;

            Int32 check = cmd.ExecuteNonQuery();
            string khoa = cmd.Parameters["@CategoryID"].Value.ToString();
            Int32 CategoryID = JamesThew.Includes.Core.ParseInt(khoa);

            cat.CategoryID = CategoryID;

            con.Close();
            if (check > 0) return true;
            return false;

        }


        public Boolean Update(Category cat)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("UpdateCategory", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@CategoryID", cat.CategoryID));
            cmd.Parameters.Add(new SqlParameter("@CategoryName", cat.CategoryName));
            cmd.Parameters.Add(new SqlParameter("@CategoryDescription", cat.CategoryDescription));
            cmd.Parameters.Add(new SqlParameter("@CategoryParent", cat.CategoryParent));
            cmd.Parameters.Add(new SqlParameter("@PostCount", cat.PostCount));


            Int32 check = cmd.ExecuteNonQuery();
            con.Close();
            if (check > 0) return true;
            return false;

        }

    }
}